﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

namespace DataAccess.SqlServer
{
    public class SqlServerHoSo : IHoSo
    {
        public HoSo GetHoSo(int oid)
        {
            string sql = @" Select MaQuanLy, Ho, Ten, GioiTinh, NgaySinh, BoPhan, NoiSinh, QueQuan, DiaChiThuongTru,
                                    NoiOHienNay, DienThoaiDiDong, DienThoaiCoQuan, Email, HopDong, TinhTrang, GhiChu
                            From HoSo
                            Where Oid = @oid";

            SqlParameter param = new SqlParameter("@oid", oid);
            DataRow row = DataProvider.GetDataRow(sql, param);

            HoSo hoSo = new HoSo();

            hoSo.Oid = oid;
            hoSo.MaQuanLy = row["MaQuanLy"].ToString();
            hoSo.Ho = row["Ho"].ToString();
            hoSo.Ten = row["Ten"].ToString();
            byte gioiTinh;
            if (byte.TryParse(row["GioiTinh"].ToString(), out gioiTinh))
                hoSo.GioiTinh = (EGioiTinh)gioiTinh;
            DateTime ngaySinh;
            if (DateTime.TryParse(row["NgaySinh"].ToString(), out ngaySinh))
                hoSo.NgaySinh = ngaySinh;

            int id;
            if (int.TryParse(row["BoPhan"].ToString(), out id))
            {
                SqlServerBoPhan sqlBoPhan = new SqlServerBoPhan();
                hoSo.BoPhan = sqlBoPhan.GetBoPhan(id);
            }
            hoSo.NoiSinh = row["NoiSinh"].ToString();
            hoSo.QueQuan = row["QueQuan"].ToString();
            hoSo.DiaChiThuongTru = row["DiaChiThuongTru"].ToString();
            hoSo.NoiOHienNay = row["NoiOHienNay"].ToString();
            hoSo.DienThoaiDiDong = row["DienThoaiDiDong"].ToString();
            hoSo.DienThoaiCoQuan = row["DienThoaiCoQuan"].ToString();
            hoSo.Email = row["Email"].ToString();
           
            if (int.TryParse(row["HopDong"].ToString(), out id))
            {
                SqlServerHopDongNhanVien hopDongNhanVien = new SqlServerHopDongNhanVien();
                hoSo.HopDong = HopDongFactory.GetHopDong(id);
            }
            if (int.TryParse(row["TinhTrang"].ToString(), out id))
            {
                SqlServerTinhTrang sqlServerTinhTrang = new SqlServerTinhTrang();
                hoSo.TinhTrang = sqlServerTinhTrang.GetTinhTrang(id);
            }
            hoSo.GhiChu = row["GhiChu"].ToString();

            return hoSo;
        }
        public int Insert(HoSo hoSo)
        {
            string sql = @" Insert into HoSo(MaQuanLy, Ho, Ten, GioiTinh, NgaySinh, BoPhan, NoiSinh, QueQuan, DiaChiThuongTru,
                                            NoiOHienNay, DienThoaiDiDong, DienThoaiCoQuan, Email,  TinhTrang, GhiChu)
                            Values(@MaQuanLy, @Ho, @Ten, @GioiTinh, @NgaySinh, @BoPhan, @NoiSinh, @QueQuan, @DiaChiThuongTru,
                                            @NoiOHienNay, @DienThoaiDiDong, @DienThoaiCoQuan, @Email, @TinhTrang, @GhiChu)";
            SqlParameter[] param = {
                                       new SqlParameter("@MaQuanLy", hoSo.MaQuanLy),
                                       new SqlParameter("@Ho", hoSo.Ho),
                                       new SqlParameter("@Ten", hoSo.Ten),
                                       new SqlParameter("@GioiTinh",hoSo.GioiTinh),
                                       new SqlParameter("@NgaySinh", hoSo.NgaySinh),
                                       new SqlParameter("@BoPhan", hoSo.BoPhan.Oid),
                                       new SqlParameter("@NoiSinh", hoSo.NoiSinh),
                                       new SqlParameter("@QueQuan", hoSo.QueQuan),
                                       new SqlParameter("@DiaChiThuongTru", hoSo.DiaChiThuongTru),
                                       new SqlParameter("@NoiOHienNay", hoSo.NoiOHienNay),
                                       new SqlParameter("@DienThoaiDiDong", hoSo.DienThoaiDiDong),
                                       new SqlParameter("@DienThoaiCoQuan",hoSo.DienThoaiCoQuan),
                                       new SqlParameter("@Email", hoSo.Email),
                                       new SqlParameter("@TinhTrang", hoSo.TinhTrang.Oid),
                                       new SqlParameter("@GhiChu", hoSo.GhiChu)
                                   };
            int id= DataProvider.Insert(sql, true, param);
            if (id > 0)
                hoSo.Oid = id;
            return hoSo.Oid;
        }

        public int Update(HoSo hoSo)
        {
            string sql = @" Update HoSo
                            Set MaQuanLy = @MaQuanLy, 
                                Ho = @Ho, 
                                Ten = @Ten, 
                                GioiTinh = @GioiTinh, 
                                NgaySinh = @NgaySinh, 
                                BoPhan = @BoPhan, 
                                NoiSinh =@NoiSinh, 
                                QueQuan = @QueQuan, 
                                DiaChiThuongTru = @DiaChiThuongTru,
                                NoiOHienNay = @NoiOHienNay, 
                                DienThoaiDiDong = @DienThoaiDiDong, 
                                DienThoaiCoQuan = @DienThoaiCoQuan, 
                                Email = @Email, 
                                HopDong = @HopDong, 
                                TinhTrang = @TinhTrang, 
                                GhiChu = @GhiChu
                            Where Oid = @Oid";
            SqlParameter[] param = {
                                       new SqlParameter("@Oid", hoSo.Oid),
                                       new SqlParameter("@MaQuanLy", hoSo.MaQuanLy),
                                       new SqlParameter("@Ho", hoSo.Ho),
                                       new SqlParameter("@Ten", hoSo.Ten),
                                       new SqlParameter("@GioiTinh",hoSo.GioiTinh),
                                       new SqlParameter("@NgaySinh", hoSo.NgaySinh),
                                       new SqlParameter("@BoPhan", hoSo.BoPhan.Oid),
                                       new SqlParameter("@NoiSinh", hoSo.NoiSinh),
                                       new SqlParameter("@QueQuan", hoSo.QueQuan),
                                       new SqlParameter("@DiaChiThuongTru", hoSo.DiaChiThuongTru),
                                       new SqlParameter("@NoiOHienNay", hoSo.NoiOHienNay),
                                       new SqlParameter("@DienThoaiDiDong", hoSo.DienThoaiDiDong),
                                       new SqlParameter("@DienThoaiCoQuan", hoSo.DienThoaiCoQuan),
                                       new SqlParameter("@Email", hoSo.Email),
                                       new SqlParameter("@HopDong", DbType.Int32),
                                       new SqlParameter("@TinhTrang", hoSo.TinhTrang.Oid),
                                       new SqlParameter("@GhiChu", hoSo.GhiChu)
                                   };

            if (hoSo.HopDong != null)
                param[14].Value = hoSo.HopDong.Oid;
            else
                param[14].Value = DBNull.Value;
            return DataProvider.Update(sql, param);
        }

        public int Delete(HoSo hoSo)
        {
            string sql = @" Delete From HoSo Where Oid = @Oid";
            SqlParameter param = new SqlParameter("@Oid", hoSo.Oid);

            return DataProvider.Update(sql, param);
        }



        public int Save(HoSo obj)
        {
            throw new NotImplementedException();
        }
    }
}
